Google Apps Script範例

輸出、取得Sheet

code:javascript

// Sheet 網址裡的 SHEET_ID 部份

//

// col1 col2

// row1 ----> | |

// row2 ----> | |

// v v

const SpreadSheetID = "";

const SpreadSheet = SpreadsheetApp.openById(SpreadSheetID);

function textOutput(obj, mimeType = "JSON") {

return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(

);

}

function getSheet(sheetName) {

return SpreadSheet.getSheetByName(sheetName);

}

doGet()

getSheetValues(startRow, startColumn, numRows, numColumns)

第一行是資料型別，因此從第二行開始取得

並且從總長度扣掉第一行

code:javascript

// ----------

// doGet

// ----------

function getAllSheetData(sheetName) {

const Sheet = getSheet(sheetName);

return Sheet.getSheetValues(

2,

1,

Sheet.getLastRow() - 1,

Sheet.getLastColumn()

);

}

function getSheetDataByRange(sheetName, range) {

const Sheet = getSheet(sheetName);

// wouldn't return category row (the first row) by default

const startRow = range.startRow ? range.startRow : 2;

const startCol = range.startCol ? range.startCol : 1;

let rowRange =

range.endRow && range.startRow

? range.endRow - range.startRow + 1

: Sheet.getLastRow() - 1;

let colRange =

range.endCol && range.startCol

? range.endCol - range.startCol + 1

: Sheet.getLastColumn();

if (rowRange > Sheet.getLastRow()) rowRange = Sheet.getLastRow() - 1;

if (colRange > Sheet.getLastColumn()) colRange = Sheet.getLastColumn();

const data = Sheet.getSheetValues(startRow, startCol, rowRange, colRange);

return data;

}

// would return

// {

// }

function getSheetDataBySpecificColumns(sheetName, columnsArray) {

const Sheet = getSheet(sheetName);

const data = {};

columnsArray.forEach((column) => {

// skip the first row

});

return data;

}

function doGet(e) {

// accept object as parameter

const params = e?.parameter;

if (!params) {

return textOutput({ response: "200" });

}

// you can customize what value to use for different execution

// and the object key name

// like params.exec or case "getAllData"

switch (params.action) {

case "getAll":

return textOutput(getAllSheetData("Sheet1"));

break;

case "getSpecificColumns":

// accept array-like input and parse it

return textOutput(

getSheetDataBySpecificColumns("Sheet1", JSON.parse(params.columnsArray))

);

break;

default:

return textOutput({ response: "200" });

break;

}

}

doPost()

code:javascript

// ----------

// doPost

// ----------

function appendSheetRow(sheetName, newRowData) {

const Sheet = getSheet(sheetName);

const newRow = Sheet.getLastRow() + 1;

// accept numeric key as row-col index

// for example,

// data: { 1: "Cell 1 data", 2: "Cell 2 data", 3: "Cell 3 data" }

// would save to the row by the key

Sheet.getRange(newRow, index).setValue(cellData);

}

}

function editSheetSpecificCell(sheetName, row, col, value) {

getSheet(sheetName).getRange(row, col).setValue(value);

}

function deleteSheetRow(sheetName, row) {

getSheet(sheetName).deleteRow(row);

}

function doPost(e) {

// accept object as post data

const postContents = JSON.parse(e?.postData.contents);

if (!postContents) {

return textOutput({ response: "200" });

}

// you can customize the key and value for different execution

switch (postContents.action) {

case "appendRow":

// again, you can also customize the key and value for post data

appendSheetRow("Sheet1", postContents.data);

return textOutput({ response: "200" });

break;

case "editCell":

editSheetSpecificCell(

"Sheet1",

postContents.row,

postContents.col,

postContents.data

);

return textOutput({ response: "200" });

break;

case "deleteRow":

deleteSheetRow("Sheet1", postContents.row);

return textOutput({ response: "200" });

default:

return textOutput({ response: "200" });

break;

}

}

客戶端

獲取資料

送出GET請求

可透過網址傳送參數

code:javascript

const apiUrl = "";

const action = "getSpecificColumns";

// 若網址參數內有另外傳入結構式資料

// 例如傳入陣列，須做 JSON.stringify()

// GAS 處也要進行 JSON.parse() 處理

const getParameter = `?action=${action}

&columnsArray=${JSON.stringify(columnsArray)}`;

await fetch(apiUrl + getParameter, {

method: "GET",

})

.then((response) => {

return response.json();

})

.then((data) => {

// 處理取得資料

console.log(data);

})

.catch((error) => {

console.log(error);

});

傳送資料

送出POST請求

資料透過請求傳送

code:javascript

const apiUrl = "";

const postData = {

action: "appendRow",

data: {

1: "some data to post",

},

};

await fetch(apiUrl, {

method: "POST",

body: JSON.stringify(postData),

headers: {

"content-type": "text/plain;charset=utf-8",

},

redirect: "follow",

});